/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset


* -------
* Table 1
* -------

* Matrix
mat table1 = J(25,2,.)
mat table1_T = J(25,1,.)

* Loop for the prevalence of excess interaction
local names `" "c" "nr" "'
local val = 1
forval i=1(1)2 {

	local x: word `i' of `names'

	* Non-randomly assigned
	* ---------------------

	* Values
	** After initial assignment
	qui: tab sig_`x'_i if sample_reg==1, matcell(initial) // distribution within sample, save frequencies
	mat table1[`val',1] = initial[2,1]
	qui: tab sig_`x'_i if sample_reg==1 // distribution within sample
	mat table1[`val',2] = r(N)
	mat table1_T[`val',1] = table1[`val',1]/table1[`val',2]

	local val = `val'+1

	** After final assignment
	qui: tab sig_`x'_f if sample_reg==1, matcell(final) // distribution within sample, save frequencies
	mat table1[`val',1] = final[2,1]
	qui: tab sig_`x'_f if sample_reg==1 // distribution within sample
	mat table1[`val',2] = r(N)
	mat table1_T[`val',1] = table1[`val',1]/table1[`val',2]

	local val = `val'+3


	* At least one non-random declaration: inspectors
	* -----------------------------------------------
	
	* Dataset
	preserve
	keep if sample_reg==1 // keep necessary observations
	collapse (sum) sig_`x'_f , by(nc_sem insp_f) // collapse by inspector-semester
	gen insps = 1 // create an identifier for future collapse
	gen collusive = 1 if sig_`x'_f>0 // create identifier of collusive 
	collapse (sum) insps collusive , by(nc_sem) // collapse by semester
	
	* Values
	qui: sum collusive // capture summary statistics
	mat table1[`val',1] = r(mean)
	qui: sum insps // capture summary statistics
	mat table1[`val',2] = r(mean)
	mat table1_T[`val',1] = table1[`val',1]/table1[`val',2]
	
	restore

	local val = `val'+1


	* At least one non-random declaration: brokers
	* --------------------------------------------
	
	* Dataset
	preserve
	keep if sample_reg==1 // keep necessary observations
	collapse (sum) sig_`x'_f , by(nc_sem key_dec) // collapse by broker-semester 
	gen insps = 1 // create an identifier for future collapse
	gen collusive = 1 if sig_`x'_f>0 // create identifier of corruption
	collapse (sum) insps collusive , by(nc_sem) // collapse by semester
	
	* Values
	qui: sum collusive // capture summary statistics
	mat table1[`val',1] = r(mean)
	qui: sum insps // capture summary statistics
	mat table1[`val',2] = r(mean)
	mat table1_T[`val',1] = table1[`val',1]/table1[`val',2]
	
	restore

	local val = `val'+1


	* At least one non-random declaration: inspector-broker pairs
	* -----------------------------------------------------------
	
	* Dataset
	preserve
	keep if sample_reg==1 // keep necessary observations
	collapse (sum) sig_`x'_f , by(nc_sem key_dec insp_f) // collapse by inspector-broker-semester
	gen insps = 1 // create an identifier for future collapse
	gen collusive = 1 if sig_`x'_f>0 // create identifier of corruption 
	collapse (sum) insps collusive , by(nc_sem) // collapse by semester
	
	* Values
	qui: sum collusive // capture summary statistics
	mat table1[`val',1] = r(mean)
	qui: sum insps // capture summary statistics
	mat table1[`val',2] = r(mean)
	mat table1_T[`val',1] = table1[`val',1]/table1[`val',2]
	
	restore

	local val = `val'+5
}

* Loop for the Joint significance of broker FE
local names `" "i" "f" "'
local val = 21
forval i=1(1)2 {

	local x: word `i' of `names'
	
	* Dataset
	preserve
	keep if sample_reg==1 // keep necessary observations
	collapse (mean) sig_j_nr_f sig_j_nr_i, by(nc_sem insp_f) // collapse by inspector-semester
	gen N = 1 // create an identifier for future collapse
	collapse (sum) sig_j_nr_f sig_j_nr_i N , by(nc_sem) // collapse by semester

	* Values
	qui: sum sig_j_nr_`x' // capture summary statistics
	mat table1[`val',1] = r(mean)
	qui: sum N // capture summary statistics
	mat table1[`val',2] = r(mean)
	mat table1_T[`val',1] = table1[`val',1]/table1[`val',2]
	
	restore

	local val = `val'+1
}


* Export
* ------
putexcel set "$tables\Table 1.xlsx", replace sheet(Table1) // create a new excel
putexcel B5 = matrix(table1), nformat(number_sep) hcenter vcenter
putexcel D5 = matrix(table1_T), nformat(0.0%) hcenter vcenter

* Title
putexcel (A1:D1), merge hcenter vcenter
putexcel A1 = "Before delegated randomization of inspector assignment"

* Panel A
putexcel (A2:D2), merge hcenter vcenter bold
putexcel A2 = "A. Prevalence of excess interaction (i.e., non-random assignment) - calibrated"
putexcel B3 = "Number", hcenter vcenter
putexcel C3 = "Number", hcenter vcenter
putexcel D3 = "%", hcenter vcenter
putexcel B4 = "Non-randomly assigned", hcenter vcenter
putexcel C4 = "Total", hcenter vcenter
putexcel B8 = "At least one non-randomly assigned declaration" , hcenter vcenter
putexcel C8 = "Total", hcenter vcenter
putexcel A5 = "Declarations - after initial assignment", vcenter left
putexcel A6 = "Declarations - after final assignment", vcenter left
putexcel A8 = "Average per semester", vcenter left italic
putexcel A9 = "Inspectors", vcenter left
putexcel A10 = "Brokers", vcenter left
putexcel A11 = "Inspector-broker pairs", vcenter left

* Panel B
putexcel (A13:D13), merge hcenter vcenter bold
putexcel A13 = "B. Prevalence of excess interaction (i.e., non-random assignment) - inspector logits"
putexcel B14 = "Number", hcenter vcenter
putexcel C14 = "Number", hcenter vcenter
putexcel D14 = "%", hcenter vcenter
putexcel B15 = "Non-randomly assigned", hcenter vcenter
putexcel C15 = "Total", hcenter vcenter
putexcel B19 = "At least one non-randomly assigned declaration" , hcenter vcenter
putexcel C19 = "Total", hcenter vcenter
putexcel B24 = "Broker fixed effects jointly sgnificant", hcenter vcenter
putexcel C24 = "Total", hcenter vcenter
putexcel A16 = "Declarations - after initial assignment", vcenter left
putexcel A17 = "Declarations - after final assignment", vcenter left
putexcel A19 = "Average per semester", vcenter left italic
putexcel A20 = "Inspectors", vcenter left
putexcel A21 = "Brokers", vcenter left
putexcel A22 = "Inspector-broker pairs", vcenter left
putexcel A24 = "Average per semester", vcenter left italic
putexcel A25 = "Inspectors - initial assignment", vcenter left
putexcel A26 = "Inspectors - final assignment", vcenter left


* -------------------------------- ENDS HERE -------------------------------- *